package com.api.gbhg.wangc.dao;

import cn.hutool.core.util.StrUtil;
import com.api.gbhg.liuwenbo.api.NonCentralTreeApi;
import com.api.gbhg.liuwenbo.dao.DataBaseUtil;
import com.api.gbhg.liuwenbo.dao.TreeStuctureDBUtil;
import com.google.common.base.Joiner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import weaver.conn.RecordSet;
import weaver.conn.RecordSetTrans;
import weaver.general.BaseBean;

import java.io.IOException;
import java.io.InputStreamReader;
import java.util.*;
import java.util.Properties;

/**
 * @author wangc
 * @description BudgetManagementDao
 * @date 2021/1/20 11:01
 */
public class BudgetManagementDao {
    public static final Logger logger = LoggerFactory.getLogger("gbhg");
    private static String sysadmin; //#查看所有权限(角色)
    private static List<String> roleStatus1; //#查看所有权限(角色)
    private static List<String> status1; //#查看所有权限(部门)
    private static List<String> status2; //#可查看整个缉私局(部门)
    private static List<String> status3; //#中山海关不可查看的分部(部门)
    private static String jisiju; //#中山海关不可查看的分部(部门)
    private static List<String> status4;
    private static List<String> status5;
//
//    static{
//            BaseBean baseBean = new BaseBean();
//            sysadmin = baseBean.getPropValue("BudgetManagement","sysadmin");
//            roleStatus1 = Arrays.asList(baseBean.getPropValue("BudgetManagement","rolestatus1").split(","));
//            status1 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status1").split(","));
//            status2 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status2").split(","));
//            status3 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status3").split(","));
//            jisiju = baseBean.getPropValue("BudgetManagement","jisiju");
//    }

    /**
     * 查询经费版本主表数据
     * @param proName
     * @param unitName
     * @param startDate
     * @param endDate
     * @param pageNum
     * @param pageSize
     * @param userid
     * @return
     */
    public RecordSetTrans getFundingVersion(String proName, String unitName, String startDate, String endDate, Integer pageNum, Integer pageSize,String userid,String sbdw,String year,String dlhsdw) {

        BaseBean baseBean = new BaseBean();
        sysadmin = baseBean.getPropValue("BudgetManagement","sysadmin");
        roleStatus1 = Arrays.asList(baseBean.getPropValue("BudgetManagement","rolestatus1").split(","));
        status1 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status1").split(","));
        status2 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status2").split(","));
        status3 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status3").split(","));
        jisiju = baseBean.getPropValue("BudgetManagement","jisiju");
        status4 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status4").split(","));
        status5 = Arrays.asList(baseBean.getPropValue("BudgetManagement","status5").split(","));

        RecordSetTrans rts = new RecordSetTrans();
        StringBuffer sb = new StringBuffer();
        RecordSet getSbdw = new RecordSet();
        if (sysadmin.equals(userid)){ //是管理员则不查询以下信息
        }

        //部门
        String department = TreeStuctureDBUtil.getDepartmentIdByUserId(Integer.valueOf(userid));
        //矩阵
        List<String> subDefinedId = TreeStuctureDBUtil.getSubcompanyDefinedByUserId(Integer.valueOf(userid));

        try {
            String sql = "select * from uf_jingfei_version where lcztz = 3 ";
            sb.append(sql);
            if (StrUtil.isNotEmpty(proName)) {
                sb.append(" AND xmmin Like '%"+proName+"%'");
            }
            if (StrUtil.isNotEmpty(unitName)) {
                sb.append(" AND CHARINDEX(','+ '"+unitName+"' +',',','+ CONVERT(varchar(200),sydw) +',')>0 ");
            }
            if (StrUtil.isNotEmpty(startDate) && StrUtil.isNotEmpty(endDate)) {
                sb.append(" AND modedatacreatedate >= ").append(startDate).append(" AND modedatacreatedate <=").append(endDate);
            }
            if (StrUtil.isNotEmpty(year)) {
                sb.append(" and ysnd=" + year);
            }
            if (StrUtil.isNotEmpty(dlhsdw)) {
                sb.append(" and dlhsdw in(" + dlhsdw+")");
            }

            if (sysadmin.equals(userid) || roleStatus1.contains(userid) || status1.contains(department)){ //查看所有
                logger.info("查看所有单位");
                if(StrUtil.isNotEmpty(sbdw)){
                    sb.append(" and sbdw="+sbdw);
                }
            }else if(status2.contains(department)){     //查看整个缉私局和当前单位
                //List<String> jisijuXjId = DataBaseUtil.getSupSubcomIdBySubId(jisiju); //缉私局下级id
                //sb.append(" and sbdw in ("+Joiner.on(",").join((Iterable<?>) jisijuXjId)+") ");
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select id from HrmSubCompany where (id=29 or supsubcomid=29 or id=(select subcompanyid1 from hrmresource where id="+userid+")) and canceled<>1");
                while (getSbdw.next()){
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                logger.info("查看整个缉私局和当前单位 申报单位范围："+Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if(StrUtil.isNotEmpty(sbdw)){
                    if(sbdwViewStr.contains(sbdw)){
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw="+sbdw);
                    }else {
                        sb.append(" and sbdw=null");
                    }
                }else{
                    sb.append(" and sbdw in (select id from HrmSubCompany where (id=29 or supsubcomid=29 or id=(select subcompanyid1 from hrmresource where id="+userid+")) and canceled<>1)");
                }
            }else if(status4.contains(department)){     //查看拱北海关本级或支出资金独立核算单位为拱北海关
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select id from HrmSubCompany where canceled<>1 and id not in (select dlhsdw from  uf_dlhsdwgl) and supsubcomid not in (5,29)");
                while (getSbdw.next()){
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                logger.info("查看拱北海关本级 申报单位范围："+Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if(StrUtil.isNotEmpty(sbdw)){
                    if(sbdwViewStr.contains(sbdw)){
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw="+sbdw);
                    }else {
                        sb.append(" and sbdw=null");
                    }
                }else {
                    sb.append(" and ( sbdw in (select id from HrmSubCompany where canceled<>1 and id not in (select dlhsdw from  uf_dlhsdwgl) and supsubcomid not in (5,29))  or dlhsdw=1 )");
                }
            }else if(status5.contains(department)){     //查看缉私局
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select id from HrmSubCompany where (id=29 or supsubcomid=29)");
                while (getSbdw.next()){
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                System.err.println("查看缉私局 申报单位范围："+Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if(StrUtil.isNotEmpty(sbdw)){
                    if(sbdwViewStr.contains(sbdw)){
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw="+sbdw);
                    }else {
                        sb.append(" and sbdw=null");
                    }
                }else{
                    sb.append(" and sbdw in (select id from HrmSubCompany where (id=29 or supsubcomid=29))");
                }
            }else{
                /*
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select subcompanyid1 from hrmresource where id="+userid);
                while (getSbdw.next()){
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                System.err.println("申报单位范围："+Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if(StrUtil.isNotEmpty(sbdw)){
                    if(sbdwViewStr.contains(sbdw)){
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw="+sbdw);
                    }else {
                        sb.append(" and sbdw=null");
                    }
                }else{
                    sb.append(" and sbdw = (select subcompanyid1 from hrmresource where id="+userid+")");
                }
                */
                sb.append(" and sbdw = (select subcompanyid1 from hrmresource where id="+userid+")");
            }

            if (null != pageNum && null != pageSize) {
                //通过分页公式计算下一页在哪一行开始
                //如1，10，1-1 = 0，那第一条开始显示下面十条，第一页
                //如2，10，(2-1)乘以10 = 10，那就是第十条开始显示下面十条，第二页
                pageNum = (pageNum - 1) * pageSize;
                // 判断是mysql 还是sqlserver
                if ("sqlserver".equals(rts.getDBType())) {
                    sb.append(" order by Id desc offset ").append(pageNum).append(" rows fetch next ").append(pageSize).append(" rows only");
                } else if ("mysql".equals(rts.getDBType())) {
                    sb.append(" LIMIT ").append(pageNum).append(",").append(pageSize);
                }
            }
            logger.info("经费版本主表sql:--->" + sb.toString());
            rts.executeQuery(sb.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rts;
    }

    //获取数据总数
    public int getMainTableCount(String proName, String unitName, String startDate, String endDate,String userid,String sbdw,String year,String dlhsdw) {
        
        RecordSet rs = new RecordSet();
        RecordSet getSbdw = new RecordSet();

        //部门
        String department = TreeStuctureDBUtil.getDepartmentIdByUserId(Integer.valueOf(userid));

        StringBuffer sb = new StringBuffer();
        String sql = "select count(*) AS total from uf_jingfei_version where lcztz = '3' ";
        try {
            //String sql = "select * from uf_jingfei_version where lcztz = 3 ";
            sb.append(sql);
            if (StrUtil.isNotEmpty(proName)) {
                sb.append(" AND xmmin Like '%" + proName + "%'");
            }
            if (StrUtil.isNotEmpty(unitName)) {
                sb.append(" AND CHARINDEX(','+ '" + unitName + "' +',',','+ CONVERT(varchar(200),sydw) +',')>0 ");
            }
            if (StrUtil.isNotEmpty(startDate) && StrUtil.isNotEmpty(endDate)) {
                sb.append(" AND modedatacreatedate >= ").append(startDate).append(" AND modedatacreatedate <=").append(endDate);
            }

            if (StrUtil.isNotEmpty(year)) {
                sb.append(" and ysnd=" + year);
            }
            if (StrUtil.isNotEmpty(dlhsdw)) {
                sb.append(" and dlhsdw in(" + dlhsdw+")");
            }

            if (sysadmin.equals(userid) || roleStatus1.contains(userid) || status1.contains(department)) { //查看所有
                logger.info("查看所有单位");
                if (StrUtil.isNotEmpty(sbdw)) {
                    sb.append(" and sbdw=" + sbdw);
                }

            } else if (status2.contains(department)) {     //查看整个缉私局和当前单位
                //List<String> jisijuXjId = DataBaseUtil.getSupSubcomIdBySubId(jisiju); //缉私局下级id
                //sb.append(" and sbdw in ("+Joiner.on(",").join((Iterable<?>) jisijuXjId)+") ");
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select id from HrmSubCompany where (id=29 or supsubcomid=29 or id=(select subcompanyid1 from hrmresource where id=" + userid + ")) and canceled<>1");
                while (getSbdw.next()) {
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                logger.info("查看整个缉私局和当前单位 申报单位范围：" + Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if (StrUtil.isNotEmpty(sbdw)) {
                    if (sbdwViewStr.contains(sbdw)) {
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw=" + sbdw);
                    } else {
                        sb.append(" and sbdw=null");
                    }
                } else {
                    sb.append(" and sbdw in (select id from HrmSubCompany where (id=29 or supsubcomid=29 or id=(select subcompanyid1 from hrmresource where id=" + userid + ")) and canceled<>1)");
                }
            } else if (status4.contains(department)) {     //查看拱北海关本级或支出资金独立核算单位为拱北海关
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select id from HrmSubCompany where canceled<>1 and id not in (select dlhsdw from  uf_dlhsdwgl) and supsubcomid not in (5,29)");
                while (getSbdw.next()) {
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                logger.info("查看拱北海关本级 申报单位范围：" + Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if (StrUtil.isNotEmpty(sbdw)) {
                    if (sbdwViewStr.contains(sbdw)) {
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw=" + sbdw);
                    } else {
                        sb.append(" and sbdw=null");
                    }
                } else {
                    sb.append(" and ( sbdw in (select id from HrmSubCompany where canceled<>1 and id not in (select dlhsdw from  uf_dlhsdwgl) and supsubcomid not in (5,29))  or dlhsdw=1 )");
                }
            } else if (status5.contains(department)) {     //查看缉私局
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select id from HrmSubCompany where (id=29 or supsubcomid=29)");
                while (getSbdw.next()) {
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                System.err.println("查看缉私局 申报单位范围：" + Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if (StrUtil.isNotEmpty(sbdw)) {
                    if (sbdwViewStr.contains(sbdw)) {
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw=" + sbdw);
                    } else {
                        sb.append(" and sbdw=null");
                    }
                } else {
                    sb.append(" and sbdw in (select id from HrmSubCompany where (id=29 or supsubcomid=29))");
                }
            } else {
                /*
                List<String> sbdwViewStr = new ArrayList<>();
                getSbdw.execute("select subcompanyid1 from hrmresource where id="+userid);
                while (getSbdw.next()){
                    sbdwViewStr.add(getSbdw.getString("id"));
                }
                System.err.println("申报单位范围："+Joiner.on(",").join((Iterable<?>) sbdwViewStr));
                if(StrUtil.isNotEmpty(sbdw)){
                    if(sbdwViewStr.contains(sbdw)){
                        //所查询申报单位符合在范围内
                        sb.append(" and sbdw="+sbdw);
                    }else {
                        sb.append(" and sbdw=null");
                    }
                }else{
                    sb.append(" and sbdw = (select subcompanyid1 from hrmresource where id="+userid+")");
                }
                */
                sb.append(" and sbdw = (select subcompanyid1 from hrmresource where id=" + userid + ")");
            }
            logger.info("经费版本主表总条数sql:--->" + sb.toString());
            rs.executeQuery(sb.toString());
            rs.next();
            return rs.getInt("total");
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 查询经费额度表
     *
     * @param requestId
     * @return RecordSetTrans
     */
    public RecordSetTrans getFundsAmount(String requestId) {
        RecordSetTrans rts = new RecordSetTrans();
        String sql = "select * from uf_jingfei where requestId = '" + requestId + "'";
        try {
            //logger.info("经费额度表sql:--->" + sql);
            rts.executeQuery(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rts;
    }

    /**
     * 根据人员ID查询姓名
     *
     * @param userId 用户ID
     * @return java.lang.String
     * @author wangc
     * @date 2021/1/20 16:14
     */
    public String getUserNameById(String userId) {
        RecordSet rs = new RecordSet();
        String sql = "select lastname from HrmResource where id = '" + userId + "'";
        //logger.info("根据人员ID查询姓名sql:--->" + sql);
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("lastname");
    }

    /**
     * 根据id获取部门名称
     *
     * @param id
     * @return java.lang.String
     * @author wangc
     * @date 2021/1/20 16:38
     */
    public String getDepartmentNameById(String id) {
        RecordSet rs = new RecordSet();
        String sql = "select departmentname from hrmdepartment where id = '" + id + "'";
        //logger.info("获取部门名称sql:--->" + sql);
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("departmentname");
    }

    /**
     * 根据id获取单位(分部)名称
     *
     * @param id
     * @return java.lang.String
     * @author wangc
     * @date 2021/1/20 16:39
     */
    public String getSubCompanyNameById(String id) {
        RecordSet rs = new RecordSet();
        String sql = "select subcompanyname from hrmsubcompany where id = '" + id + "'";
        //logger.info("获取单位名称sql:--->" + sql);
        rs.executeQuery(sql);
        rs.next();
        return rs.getString("subcompanyname");
    }
}
